<?php

class Pinjaman_model extends Model {

	var $data;
	
	function Pinjaman_model()
	{
		parent::Model();
	}
	
	function set_anggota_id($anggota_id)
	{
		$this->db->where('pinjaman.anggota_id', $anggota_id);
	}

	function get_all_data(){

		///* new
		/*ganti saldo_pinj na
		$this->db->select('pinjaman.*, anggota.nama_lengkap as nama_anggota, SUM(angsuran.biaya) as saldo_pinj');
		$this->db->from('pinjaman');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');		
		$this->db->join('angsuran', 'pinjaman.id=angsuran.no_rekening_id', 'left');
		$this->db->group_by('pinjaman.id');
		//*/
		$this->db->select('pinjaman.*, anggota.nama_lengkap as nama_anggota, COUNT(angsuran.angsuran_ke) as angke');
		$this->db->from('pinjaman');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');		
		$this->db->join('angsuran', 'pinjaman.id=angsuran.no_rekening_id', 'left');
		$this->db->group_by('pinjaman.id');
		
		/*
		select pinjaman.*, anggota.nama_lengkap as nama_anggota, angsuran.saldo_skrg from pinjaman, anggota, angsuran where pinjaman.anggota_id=anggota.id_anggota AND pinjaman.id=angsuran.no_rekening_id GROUP BY pinjaman.id
		*/
		
		/*old
		$this->db->select('pinjaman.*, anggota.nama_lengkap as nama_anggota, SUM(angsuran.biaya) as saldo_pinj, jenis_pinj.id_jenis_pinj');
		$this->db->from('pinjaman');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');
		$this->db->join('jenis_pinj', 'pinjaman.jenis_pinj=jenis_pinj.id_jenis_pinj', 'INNER');
		$this->db->join('angsuran', 'pinjaman.id=angsuran.no_rekening_id', 'left');
		$this->db->group_by('pinjaman.id');
		*/
		
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result();
		}
		else
		{
			return FALSE;
		}
	}
	
	
	function get_last_id(){
		$query = $this->db->query('select *, SUBSTR(no_rekening, 1,4) as urut from pinjaman order by urut desc limit 1 ');
		if ($query->num_rows() > 0)
		{
			return $query->row();
		}
		else
		{
			return FALSE;
		}
	}

	function get_data_by_id($id){
		$this->db->select('pinjaman.*, anggota.alamat as alamat_anggota, anggota.telpon_1, anggota.tgl_lahir as tgl_lahir_anggota, anggota.tempat_lahir as tempat_lahir_anggota, anggota.nama_lengkap as nama_anggota, anggota.alamat, anggota.pekerjaan');
		$this->db->from('pinjaman');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');
		$this->db->where('pinjaman.id', $id);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->row_array();
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_jenis_by_id($id){
		$this->db->select('pinjaman.*, anggota.nama_lengkap as nama_anggota,  anggota.alamat, anggota.pekerjaan');
		$this->db->from('pinjaman');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');
		$this->db->where('pinjaman.id', $id);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->row();
		}
		else
		{
			return FALSE;
		}
	}


	function fill_data()
	{
//		echo 'hoho';
//		echo $this->input->post('anggotaID');
		//$status = ($this->input->post('status')) ? $this->input->post('status') : 'Start';
		
		$biaya = $this->input->post('biaya');		
		$biaya = unRP($biaya);
		
		$plafond = $this->input->post('plafond');
		$plafond = unRP($plafond);
		
		$angsuran_pokok = $this->input->post('angsuran_pokok');
		$angsuran_pokok = unRP($angsuran_pokok);
		
		$angsuran_bunga = $this->input->post('angsuran_bunga');
		$angsuran_bunga = unRP($angsuran_bunga);
		
		$total_angsuran = $this->input->post('total_angsuran');
		$total_angsuran = unRP($total_angsuran);
		
		$jenis_pinj = $this->input->post('jenis_pinj');
		if($jenis_pinj == 1){
			$jenis_pinj = 'Harian';
		}else if($jenis_pinj == 2){
			$jenis_pinj = 'Mingguan';
		}else if($jenis_pinj == 3){
			$jenis_pinj = 'Bulanan';			
		}else if($jenis_pinj == 4){
			$jenis_pinj = 'Gadean';			
		}else if($jenis_pinj == 5){
			$jenis_pinj = 'P3KUM';			
		}
		
		$this->data = array(
			'jenis_pinj' => $this->input->post('jenis_pinj'),
			'nama_pinjaman' => $jenis_pinj,
			'jenis_agunan' => $this->input->post('jenis_agunan'),
			'no_rekening' => $this->input->post('no_rekening'),
			//'anggota_id' => $this->input->post('anggotaID'),
			'anggota_id' => $this->input->post('anggota'),
			'tgl_mulai' => $this->input->post('tgl_mulai'),
			'tgl_jatuh_tempo' => $this->input->post('tgl_jatuh_tempo'),
			'periode' => $this->input->post('periode'),
			'plafond' => $plafond,
			'bunga_persen' => $this->input->post('bunga_persen'),
			'bunga' => $this->input->post('bunga'),
			'angsuran_pokok' => $angsuran_pokok,
			'angsuran_bunga' => $angsuran_bunga,			
			//bpkb
			'total_angsuran' => $total_angsuran,					
			'nama_pemilik' => $this->input->post('nama_pemilik'),
			'alamat_pemilik' => $this->input->post('alamat_pemilik'),
			'no_bpkb' => $this->input->post('no_bpkb'),
			'no_reg' => $this->input->post('no_reg'),
			'no_polisi' => $this->input->post('no_polisi'),
			'merk' => $this->input->post('merk'),
			'type' => $this->input->post('type'),
			'jenis' => $this->input->post('jenis'),
			'model' => $this->input->post('model'),
			'tahun_pembuatan' => $this->input->post('tahun_pembuatan'),
			'tahun_perakitan' => $this->input->post('tahun_perakitan'),
			'isi_silinder' => $this->input->post('isi_silinder'),
			'warna' => $this->input->post('warna'),
			'no_rangka' => $this->input->post('no_rangka'),
			'no_mesin' => $this->input->post('no_mesin'),
			'roda' => $this->input->post('roda'),
			'bahan_bakar' => $this->input->post('bahan_bakar'),
			//waris
			'nama_waris' => $this->input->post('nama_waris'),
			'tempat_lahir_waris' => $this->input->post('tempat_lahir_waris'),
			'tgl_lahir_waris' => $this->input->post('tgl_lahir_waris'),
			'pekerjaan_waris' => $this->input->post('pekerjaan_waris'),
			'alamat_waris' => $this->input->post('alamat_waris'),			
			//sertifikat
			'hak_milik' => $this->input->post('hak_milik'),
			'nib' => $this->input->post('nib'),
			'asal_hak_pemisahan' => $this->input->post('asal_hak_pemisahan'),			
			'dasar_pendaftaran' => $this->input->post('dasar_pendaftaran'),			
			'surat_ukur' => $this->input->post('surat_ukur'),			
			'luas' => $this->input->post('luas'),			
			'pemegang_hak' => $this->input->post('pemegang_hak'),			
			'tgl_lahir' => $this->input->post('tgl_lahir'),			
			'pembukuan' => $this->input->post('pembukuan'),			
			'sertifikat' => $this->input->post('sertifikat'),			
			'masa_angsuran' => $this->input->post('masa_angsuran'),
			'biaya' => $biaya,
			'keterangan' => $this->input->post('keterangan')
		);
	}
	
	//Check for duplicate login ID
	function check_name($id = '')
	{
		$this->db->where('no_rekening', $this->data['no_rekening']);
		if($id != '') $this->db->where('id !=', $id);
		$query = $this->db->get('pinjaman');

		if ($query->num_rows() > 0)
		{
			return FALSE;
		}
		else
		{
			return TRUE;
		}
	}	

	function insert_data()
	{
		$insert = $this->db->insert('pinjaman', $this->data);
		return $insert;
	}

	function update_data($id)
	{
		$this->db->where('id', $id);
		$update = $this->db->update('pinjaman', $this->data);
		return $update;
	}

	function delete_data($id){
		$this->db->where('id', $id);
		$delete = $this->db->delete('pinjaman');
		return $delete;
	}
	
	
	

}
function unRP($string){
		$string = str_replace(",00", "", $string);
		$string = str_replace("Rp", "", $string);
		$string = str_replace(" ", "", $string);
		$string = str_replace(".", "", $string);
		return $string;
	}
/* End of file pinjaman_model.php */
/* Location: ./application/models/pinjaman_model.php */



